package com.ww.editsms.widget;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Logger;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @since 
 * @version 2013-4-15
 * @author wwang
 */
public class SearchDBHelper extends SQLiteOpenHelper {
	private SQLiteDatabase sqlitedb;
	public SearchDBHelper(Context context) {
		super(context, "sms.search", null, 1);
		sqlitedb = getWritableDatabase();
	}

	private static final String T_SEARCH = "t_search", SOURCE = "source", TXT = "txt", ITEM_INDEX = "item_index";

	//在数据库第一次生成的时候会调用这个方法，同时我们在这个方法里边生成数据库表.
	@Override
	public void onCreate(SQLiteDatabase db) {
		//创建数据表的操作
		db.execSQL(createTable());

	}

	// 更新或者升级数据库的时候会自动调用这个方法，一般我们会在这个方法中
	// 删除数据表，然后再创建新的数据表操作。
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	public  String createTable() {
		StringBuffer sql1 = new StringBuffer("CREATE TABLE  IF NOT EXISTS " + T_SEARCH
				+ "(id INTEGER PRIMARY KEY AUTOINCREMENT ");
		sql1.append("," + SOURCE + " text");
		sql1.append("," + TXT + " text");
		sql1.append("," + ITEM_INDEX + " text");
		sql1.append(" )");
		return sql1.toString();
	}

	/**
	 * 插入班车信息.
	 * @param lines 线路列表
	 * @param buss 车辆列表
	 * @param stations 车辆行驶路线列表
	 */
	public void save(String source, String txt) {
		try {
			String index = new Date().getTime() + "";
			ContentValues cvD = new ContentValues();
			cvD.put(SOURCE, source);
			cvD.put(TXT, txt);
			cvD.put(ITEM_INDEX, index);

			save(T_SEARCH, cvD, SOURCE + "=? and " + TXT + "=?", new String[] { source, txt });
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 获取线路信息.
	 * @return List<Line>
	 */
	public List<String> getList(String source) {
		List<String> list = new ArrayList<String>();
		try {
			Cursor cur = query(T_SEARCH, SOURCE + "=? order by " + ITEM_INDEX + " desc limit 0,10",
					new String[] { source });
			if (cur != null) {
				while (cur.moveToNext()) {
					list.add(cur.getString(2));
				}
			}
			cur.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDb();
		}
		return list;
	}

	/**
	 * 插入班车信息.
	 * @param lines 线路列表
	 * @param buss 车辆列表
	 * @param stations 车辆行驶路线列表
	 */
	public boolean delete(String source, String txt) {
		try {
			delete(T_SEARCH, SOURCE + "=? and " + TXT + "=?", new String[] { source, txt });
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	
	
	//-------------------------------------------------------------
	/**
	 * 保存数据.
	 * @param tableName 表
	 * @param cvD  ContentValues
	 * @param where 条件
	 * @param value 值
	 */
	public void save(String tableName, ContentValues cvD, String where, String[] value) {
		try {
			if (checkExsit(tableName, where, value)) {
				openDb();
				sqlitedb.update(tableName, cvD, where, value);
			} else {
				openDb();
				sqlitedb.insert(tableName, null, cvD);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDb();
		}

	}

	/**
	 * 判断是否存在.	
	 * @param tableName 表
	 * @param where 条件
	 * @param value 值
	 */
	public boolean checkExsit(String tableName, String where, String[] value) {
		boolean bool = false;
		openDb();
		Cursor cur = sqlitedb.rawQuery("SELECT * FROM " + tableName + " WHERE " + where, value);
		if (cur != null) {
			bool = cur.getCount() > 0;
			cur.close();
			closeDb();
		}
		return bool;
	}

	/**
	 * 查询数据.
	 * 注意:使用完必须关闭 Cursor和数据库连接池<br>
	 * cur.close();
	 * sqlitedb.close();
	 * @param tableName 表
	 * @param where 条件 ( xxxx=? and yyy=?)
	 * @param value 值 (new String[]{"xxx","yyy"})
	 * @return Cursor
	 */
	public Cursor query(String tableName, String where, String[] value) {
		Cursor cur = null;
		try {
			openDb();
			if (where != null) {
				String sql = "SELECT * FROM " + tableName + " WHERE " + where;
				cur = sqlitedb.rawQuery(sql, value);
			} else {
				String sql = "SELECT * FROM " + tableName;
				cur = sqlitedb.rawQuery(sql, null);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cur;
	}

	/**
	 * 获取最新的一条数据.	
	 * @param tableName 表
	 * @param value 排序字段
	 * @return  
	 */
	public String getLast(String tableName, String value) {
		Cursor cur = null;
		String str = null;
		try {
			cur = sqlitedb.rawQuery(
					"SELECT " + value + " FROM " + tableName + " ORDER BY " + value + " DESC limit 0,1", null);
			if (cur != null) {
				while (cur.moveToNext()) {
					str = cur.getString(0);
				}
			}
		} catch (Exception e) {
		} finally {
			if (cur != null) {
				cur.close();
			}
			closeDb();
		}
		return str;
	}

	/**
	 * 删除数据.
	 * @param tableName 表
	 * @param where 条件 ( xxxx=? and yyy=?)
	 * @param value 值 (new String[]{"xxx","yyy"})
	 * @return boolean
	 */
	public boolean delete(String tableName, String where, String[] value) {
		try {
			openDb();
			sqlitedb.delete(tableName, where, value);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDb();
		}
		return false;
	}

	/**
	 * 关闭数据库连接池.
	 */
	public void closeDb() {
		if (sqlitedb.isOpen()) {
			sqlitedb.close();
		}
	}

	/**
	 * 开启数据库连接池.
	 */
	private void openDb() {
		if (!sqlitedb.isOpen()) {
			sqlitedb = getWritableDatabase();
		}
	}

}
